import aiosqlite
import asyncio
import os
import config



class sqlitedb(object):
    def __init__(self,loop):
        self.SQLITE_FILE_PATH = os.path.join(config.mypath,'data.db')
        self.CUR_DB = None

    async def init_table(self):
        self.CUR_DB = await aiosqlite.connect(self.SQLITE_FILE_PATH,loop=asyncio.get_event_loop())

        ret = await self.CUR_DB.execute("select name from sqlite_master where type='table' and name='myid'")
        if not await ret.fetchall():
            await self.CUR_DB.execute('''CREATE TABLE myid(
                mid INTEGER PRIMARY KEY        NOT NULL,
                name       VARCHAR(255)        NOT NULL,
                note       VARCHAR(255),
                mtime      VARCHAR(10),
                mmoney     INT(10),
                msum       INT(10));''')

        ret = await self.CUR_DB.execute("select name from sqlite_master where type='table' and name='mydata'")
        if not await ret.fetchall():
            await self.CUR_DB.execute('''CREATE TABLE mydata(
                id INTEGER PRIMARY KEY    AUTOINCREMENT,
                start_time VARCHAR(10)         NOT NULL,
                stop_time  VARCHAR(10)         NOT NULL,
                write_time VARCHAR(20)         NOT NULL,
                mbool      VARCHAR(1)          NOT NULL,
                note       VARCHAR(255),
                mmoney     FLOAT(10),
                msum       INT(10),
                mid        INTEGER,  
                CONSTRAINT mid  
                FOREIGN KEY (mid)  
                REFERENCES myiddata(mid));''')

        ret = await self.CUR_DB.execute("select name from sqlite_master where type='table' and name='myjizhang'")
        if not await ret.fetchall():
            await self.CUR_DB.execute('''CREATE TABLE myjizhang(
                id INTEGER PRIMARY KEY    AUTOINCREMENT,
                myear      int(4)             NOT NULL,
                mmonth     int(2)             NOT NULL,
                mday       int(2)             NOT NULL,
                mdata      int(10)            NOT NULL
                );''')

        await self.CUR_DB.commit()

    async def select_myjizhang(self,year,month,day):
        if day:
            sql = "SELECT * FROM myjizhang WHERE myear={} AND mmonth={} AND mday={}".format(year,month,day)
        else:
            sql = "SELECT * FROM myjizhang WHERE myear={} AND mmonth={}".format(year,month)
        ret = await self.sql_run(sql)
        if ret:
            return await ret.fetchall()

    async def insert_myjizhang(self,year,month,day,mony):
        sql = "INSERT INTO myjizhang VALUES (null,{},{},{},{})".format(year,month,day,mony)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def update_myjizhang(self,mony,year,month,day):
        sql = "UPDATE myjizhang SET mdata={} WHERE myear={} AND mmonth={} AND mday={}".format(mony,year,month,day)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def insert_myid(self,mid,name,note,mtime,mmoney,msum):
        sql = "INSERT INTO myid VALUES ({},'{}','{}','{}',{},{})".format(mid,name,note,mtime,mmoney,msum)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def delete_myid(self,mid):
        sql = "DELETE FROM myid WHERE mid={}".format(mid)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def update_myid(self,note,mtime,mmoney,msum,mid):
        sql = "UPDATE myid SET note='{}', mtime='{}', mmoney={}, msum={} WHERE mid={}".format(note,mtime,mmoney,msum,mid)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def select_myid(self):
        sql = "SELECT * FROM myid"
        ret = await self.sql_run(sql)
        if ret:
            return await ret.fetchall()

    async def search_select_myid(self,mid):
        sql = "SELECT * FROM myid WHERE mid={}".format(mid)
        ret = await self.sql_run(sql)
        if ret:
            return await ret.fetchall()

    async def insert_mydata(self,start_time,stop_time,write_time,mbool,note,mmoney,msum,mid):
        sql = "INSERT INTO mydata VALUES(null,'{}', '{}', '{}', '{}', '{}', {}, {}, {})".format(start_time,stop_time,write_time,mbool,note,mmoney,msum,mid)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def delete_mydata(self,mid):
        sql = "DELETE FROM mydata WHERE id={}".format(mid)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def set_mydata(self,mbool,mid):
        # sql = "UPDATE mydata SET mid = (SELECT mid FROM myid WHERE mid={}) WHERE mid={}".format(new_mid,old_mid)
        sql = "UPDATE mydata SET mbool='{}' WHERE id={}".format(mbool,mid)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def update_mydata(self,start_time,stop_time,mbool,note,mmoney,msum,mid):
        # sql = "UPDATE mydata SET mid = (SELECT mid FROM myid WHERE mid={}) WHERE mid={}".format(new_mid,old_mid)
        sql = "UPDATE mydata SET start_time='{}', stop_time='{}', mbool='{}', note='{}', mmoney={}, msum={} WHERE id={}".format(start_time,stop_time,mbool,note,mmoney,msum,mid)
        ret = await self.sql_run(sql)
        if ret:
            return ret.rowcount

    async def select_mydata(self,mbool):
        sql = "SELECT s.*,t.name FROM mydata AS s INNER JOIN myid AS t ON s.mid = t.mid WHERE mbool='{}'".format(mbool)
        ret = await self.sql_run(sql)
        if ret:
            return await ret.fetchall()

    async def search_select_mydata(self,mbool,mid):
        sql = "SELECT s.*,t.name FROM mydata AS s INNER JOIN myid AS t ON s.mid = t.mid WHERE mbool='{}' AND s.mid={}".format(mbool,mid)
        ret = await self.sql_run(sql)
        if ret:
            return await ret.fetchall()

    async def search_select_mydata_id(self,mid):
        sql = "SELECT s.*,t.name FROM mydata AS s INNER JOIN myid AS t ON s.mid = t.mid WHERE id={}".format(mid)
        ret = await self.sql_run(sql)
        if ret:
            return await ret.fetchall()

    async def get_insert_id(self,tablename):
        sql = "SELECT last_insert_rowid() FROM {}".format(tablename)
        ret = await self.sql_run(sql)
        return ret.lastrowid

    async def sql_run(self,sql):
        try:
            if not self.CUR_DB:
                self.CUR_DB = await aiosqlite.connect(self.SQLITE_FILE_PATH,loop=asyncio.get_event_loop())
            ret = await self.CUR_DB.execute(sql)
            await self.CUR_DB.commit()
        except:
            config.mylog()
            ret = None
            await self.CUR_DB.rollback()
        return ret

    async def close_db(self):
        await self.CUR_DB.close()
